<template><div><h2 id="数据库访问对象-database-access-objects" tabindex="-1"><a class="header-anchor" href="#数据库访问对象-database-access-objects"><span>数据库访问对象（Database Access Objects）</span></a></h2>
<p>Yii 提供了一个建立在 PHP PDO 数据库抽象层之上的数据访问层 (DAO)。DAO 为不同的数据库提供了一套统一的API。其中 <code v-pre>ActiveRecord</code>（ORM一种选型）提供了数据库与模型(MVC 中的 M, Model) 的交互，<code v-pre>QueryBuilder</code> 用于创建动态的查询语句。DAO 提供了简单高效的 SQL 查询，可以用在与数据库交互的各个地方。</p>
<p>使用 Yii DAO 时，你主要需要处理纯 SQL 语句和 PHP 数组。因此，这是访问数据库最高效的方法。然而，因为不同数据库之间的 SQL 语法往往是不同的，使用 Yii DAO 也意味着你必须花些额外的工夫来创建一个“数据库无关”的应用。</p>
<p>Yii DAO 支持下列现成的数据库：</p>
<ul>
<li><a href="http://www.mysql.com/" target="_blank" rel="noopener noreferrer">MySQL</a></li>
<li><a href="https://mariadb.com/" target="_blank" rel="noopener noreferrer">MariaDB</a></li>
<li><a href="http://sqlite.org/" target="_blank" rel="noopener noreferrer">SQLite</a></li>
<li><a href="http://www.postgresql.org/" target="_blank" rel="noopener noreferrer">PostgreSQL</a>：版本 &gt;= 8.4</li>
<li><a href="http://www.cubrid.org/" target="_blank" rel="noopener noreferrer">CUBRID</a>：版本 &gt;= 9.3</li>
<li><a href="https://www.oracle.com/cn/database/" target="_blank" rel="noopener noreferrer">Oracle</a></li>
<li><a href="https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads" target="_blank" rel="noopener noreferrer">MSSQL</a>：版本 &gt;= 2008</li>
</ul>
<blockquote>
<p>Note: 供 PHP 7 使用的新版 pdo_oci 扩展目前仅发布了源代码，如果你想编译使用请参照 <a href="https://github.com/yiisoft/yii2/issues/10975#issuecomment-248479268" target="_blank" rel="noopener noreferrer">社区用户提供的编译安装指引</a>。 或者你也可以在你的应用中使用 <a href="https://github.com/taq/pdooci" target="_blank" rel="noopener noreferrer">PDO 模拟层</a>。</p>
</blockquote>
<h2 id="创建数据库连接" tabindex="-1"><a class="header-anchor" href="#创建数据库连接"><span>创建数据库连接</span></a></h2>
<p>想要访问数据库，你首先需要通过创建一个 [[yii\db\Connection]] 实例来与之建立连接。</p>
<blockquote>
<p>Info：当你实例化一个 DB Connection 时，直到你第一次执行 SQL 或者你明确地调用 [[yii\db\Connection::open()|open()]] 方法时，才建立起实际的数据库连接。</p>
</blockquote>
<h3 id="pdo-方式连接" tabindex="-1"><a class="header-anchor" href="#pdo-方式连接"><span>PDO 方式连接</span></a></h3>
<p>目前 PHP 最流行的 db 连接方式，具体如下：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$db</span> <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name class-name-fully-qualified">yii<span class="token punctuation">\</span>db<span class="token punctuation">\</span>Connection</span><span class="token punctuation">(</span><span class="token punctuation">[</span></span>
<span class="line">    <span class="token string single-quoted-string">'dsn'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'mysql:host=localhost;port=3306;dbname=demo'</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">'username'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'root'</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">'password'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">''</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">'charset'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'utf8mb4'</span><span class="token punctuation">,</span></span>
<span class="line"><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>因为数据库连接经常需要在多个地方使用到，一个常见的做法是以 <a href="https://learnku.com/docs/yii-framework/2.0.x/application-component/12001" target="_blank" rel="noopener noreferrer">应用组件</a> 的方式来配置它，如下:</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token keyword">return</span> <span class="token punctuation">[</span></span>
<span class="line">    <span class="token comment">// ...</span></span>
<span class="line">    <span class="token string single-quoted-string">'components'</span> <span class="token operator">=></span> <span class="token punctuation">[</span></span>
<span class="line">        <span class="token comment">// ...</span></span>
<span class="line">        <span class="token string single-quoted-string">'db'</span> <span class="token operator">=></span> <span class="token punctuation">[</span></span>
<span class="line">            <span class="token string single-quoted-string">'class'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'yii\db\Connection'</span><span class="token punctuation">,</span></span>
<span class="line">            <span class="token string single-quoted-string">'dsn'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'mysql:host=localhost;port=3306;dbname=demo'</span><span class="token punctuation">,</span></span>
<span class="line">            <span class="token string single-quoted-string">'username'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'root'</span><span class="token punctuation">,</span></span>
<span class="line">            <span class="token string single-quoted-string">'password'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">''</span><span class="token punctuation">,</span></span>
<span class="line">            <span class="token string single-quoted-string">'charset'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'utf8mb4'</span><span class="token punctuation">,</span></span>
<span class="line">        <span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token comment">// ...</span></span>
<span class="line"><span class="token punctuation">]</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>之后你就可以通过语句 <code v-pre>Yii::$app-&gt;db</code> 来使用数据库连接了。</p>
<h4 id="常用-dsn-格式" tabindex="-1"><a class="header-anchor" href="#常用-dsn-格式"><span>常用 dsn 格式</span></a></h4>
<p>配置数据库连接时，你应该总是通过 [[yii\db\Connection::dsn|dsn]] 属性来指明它的数据源名称 (DSN) 。不同的数据库有着不同的 DSN 格式。请参考 <a href="https://www.php.net/manual/zh/pdo.construct.php" target="_blank" rel="noopener noreferrer">PHP手册</a> 来获得更多细节，示例如下：</p>
<ul>
<li>MySQL，MariaDB: <code v-pre>mysql:host=localhost;port=3306;dbname=mydatabase</code></li>
<li>SQLite: <code v-pre>sqlite:/path/to/database/file</code></li>
<li>PostgreSQL: <code v-pre>pgsql:host=localhost;port=5432;dbname=mydatabase</code></li>
<li>CUBRID: <code v-pre>cubrid:dbname=demodb;host=localhost;port=33000</code></li>
<li>MS SQL Server (sqlsrv 驱动): <code v-pre>sqlsrv:Server=localhost;Database=mydatabase</code></li>
<li>MS SQL Server (dblib 驱动): <code v-pre>dblib:host=localhost;dbname=mydatabase</code></li>
<li>MS SQL Server (mssql 驱动): <code v-pre>mssql:host=localhost;dbname=mydatabase</code></li>
<li>Oracle: <code v-pre>oci:dbname=//localhost:1521/mydatabase</code></li>
</ul>
<h3 id="odbc-方式连接" tabindex="-1"><a class="header-anchor" href="#odbc-方式连接"><span>ODBC 方式连接</span></a></h3>
<p>请注意，如果你是通过 <a href="https://www.php.net/manual/zh/book.uodbc.php" target="_blank" rel="noopener noreferrer">ODBC</a> 来连接数据库，你应该配置 [[yii\db\Connection::driverName]] 属性，以便 Yii 能够知道实际的数据库种类。例如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token string single-quoted-string">'db'</span> <span class="token operator">=></span> <span class="token punctuation">[</span></span>
<span class="line">    <span class="token string single-quoted-string">'class'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'yii\db\Connection'</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">'driverName'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'mysql'</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">'dsn'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'odbc:Driver={MySQL};Server=localhost;Database=test'</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">'username'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'root'</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">'password'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">''</span><span class="token punctuation">,</span></span>
<span class="line"><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>除了 [[yii\db\Connection::dsn|dsn]] 属性， 你常常需要配置 [[yii\db\Connection::username|username]] 和 [[yii\db\Connection::password|password]]。请参考 [[yii\db\Connection]] 来获取完整的可配置属性列表。</p>
<h3 id="初始化环境变量" tabindex="-1"><a class="header-anchor" href="#初始化环境变量"><span>初始化环境变量</span></a></h3>
<p>有时，您可能想要在建立起数据库连接时，立即执行一些语句来初始化一些环境变量 (比如设置时区或者字符集等), 你可以通过为数据库连接的 [[yii\db\Connection::EVENT_AFTER_OPEN|afterOpen]] 事件注册一个事件处理器来达到目的。 具体 db 组件配置如下：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token string single-quoted-string">'db'</span> <span class="token operator">=></span> <span class="token punctuation">[</span></span>
<span class="line">     <span class="token comment">// ...</span></span>
<span class="line">     <span class="token string single-quoted-string">'on afterOpen'</span> <span class="token operator">=></span> <span class="token keyword">function</span><span class="token punctuation">(</span><span class="token variable">$event</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">         <span class="token comment">// $event->sender 指的是 DB connection 实例</span></span>
<span class="line">         <span class="token variable">$event</span><span class="token operator">-></span><span class="token property">sender</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token string double-quoted-string">"SET time_zone = 'UTC'"</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">execute</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">     <span class="token punctuation">}</span></span>
<span class="line"><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="连接多个数据库" tabindex="-1"><a class="header-anchor" href="#连接多个数据库"><span>连接多个数据库</span></a></h2>
<p>如果你的应用需要访问多个数据库，你可以配置多个 db 应用组件来实现，具体如下：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token keyword">return</span> <span class="token punctuation">[</span></span>
<span class="line">    <span class="token comment">// ...</span></span>
<span class="line">    <span class="token string single-quoted-string">'components'</span> <span class="token operator">=></span> <span class="token punctuation">[</span></span>
<span class="line">        <span class="token comment">// 数据库1</span></span>
<span class="line">        <span class="token string single-quoted-string">'db'</span> <span class="token operator">=></span> <span class="token punctuation">[</span></span>
<span class="line">            <span class="token string single-quoted-string">'class'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'yii\db\Connection'</span><span class="token punctuation">,</span></span>
<span class="line">            <span class="token string single-quoted-string">'dsn'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'mysql:host=localhost;port=3306;dbname=demo'</span><span class="token punctuation">,</span></span>
<span class="line">            <span class="token string single-quoted-string">'username'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'root'</span><span class="token punctuation">,</span></span>
<span class="line">            <span class="token string single-quoted-string">'password'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">''</span><span class="token punctuation">,</span></span>
<span class="line">            <span class="token string single-quoted-string">'charset'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'utf8mb4'</span><span class="token punctuation">,</span></span>
<span class="line">        <span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line"></span>
<span class="line">        <span class="token comment">// 数据库2</span></span>
<span class="line">        <span class="token string single-quoted-string">'dbOther'</span> <span class="token operator">=></span> <span class="token punctuation">[</span></span>
<span class="line">            <span class="token string single-quoted-string">'class'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'yii\db\Connection'</span><span class="token punctuation">,</span></span>
<span class="line">            <span class="token string single-quoted-string">'dsn'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'pgsql:host=localhost;port=5432;dbname=demo2'</span><span class="token punctuation">,</span></span>
<span class="line">            <span class="token string single-quoted-string">'username'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'root'</span><span class="token punctuation">,</span></span>
<span class="line">            <span class="token string single-quoted-string">'password'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">''</span><span class="token punctuation">,</span></span>
<span class="line">            <span class="token string single-quoted-string">'charset'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'utf8mb4'</span><span class="token punctuation">,</span></span>
<span class="line">        <span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token comment">// ...</span></span>
<span class="line"><span class="token punctuation">]</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>之后就可以这样调用刚刚配置的其他的数据库连接实例，代码如下：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">dbOther</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div></div></div><h3 id="编辑器友好支持" tabindex="-1"><a class="header-anchor" href="#编辑器友好支持"><span>编辑器友好支持</span></a></h3>
<p>如果想要得到 编辑器或 IDE 的代码自动提示补全功能，请参考前面的 <a href="https://learnku.com/docs/yii-framework/2.0.x/editor-and-ide/11977#f0a39b" title="编辑器与IDE" target="_blank" rel="noopener noreferrer">编辑器与IDE</a> 章节，在根目录的 <code v-pre>Yii.php</code> 中增加注释 <code v-pre>@property \yii\db\Connection $dbOther</code>，示例如下：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token doc-comment comment">/**</span>
<span class="line"> * Class BaseApplication</span>
<span class="line"> * Used for properties that are identical for both WebApplication and ConsoleApplication</span>
<span class="line"> * <span class="token keyword">@property</span> <span class="token class-name"><span class="token punctuation">\</span>yii<span class="token punctuation">\</span>db<span class="token punctuation">\</span>Connection</span> <span class="token parameter">$dbOther</span></span>
<span class="line"> */</span></span>
<span class="line"><span class="token keyword">abstract</span> <span class="token keyword">class</span> <span class="token class-name-definition class-name">BaseApplication</span> <span class="token keyword">extends</span> <span class="token class-name class-name-fully-qualified">yii<span class="token punctuation">\</span>base<span class="token punctuation">\</span>Application</span></span>
<span class="line"><span class="token punctuation">{</span></span>
<span class="line"><span class="token punctuation">}</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="执行-sql-查询" tabindex="-1"><a class="header-anchor" href="#执行-sql-查询"><span>执行 SQL 查询</span></a></h2>
<p>当您创建了数据库实例，就可以通过原生 SQL 、绑定参数或调用 <code v-pre>yii\db\Command</code> 里的 SQL 执行方法等三种主要方式，来执行查询。</p>
<h3 id="原生-sql-查询" tabindex="-1"><a class="header-anchor" href="#原生-sql-查询"><span>原生 SQL 查询</span></a></h3>
<p>下列例子展示了几种不同的从数据库取得数据的方法：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">//数据库连接实例化</span></span>
<span class="line"><span class="token variable">$db</span> <span class="token operator">=</span> <span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// 返回多行，每行都是列名和值的关联数组</span></span>
<span class="line"><span class="token comment">// 如果该查询没有结果则返回 空数组 []</span></span>
<span class="line"><span class="token variable">$posts</span> <span class="token operator">=</span> <span class="token variable">$db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'SELECT * FROM post'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">queryAll</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// 返回一行 (第一行)</span></span>
<span class="line"><span class="token comment">// 如果该查询没有结果则返回 false</span></span>
<span class="line"><span class="token variable">$post</span> <span class="token operator">=</span> <span class="token variable">$db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'SELECT * FROM post WHERE id=1'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">queryOne</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// 返回一列 (第一列)</span></span>
<span class="line"><span class="token comment">// 如果该查询没有结果则返回 空数组 []</span></span>
<span class="line"><span class="token variable">$titles</span> <span class="token operator">=</span> <span class="token variable">$db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'SELECT title FROM post'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">queryColumn</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// 返回一个标量值</span></span>
<span class="line"><span class="token comment">// 如果该查询没有结果则返回 false</span></span>
<span class="line"><span class="token variable">$count</span> <span class="token operator">=</span> <span class="token variable">$db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'SELECT COUNT(*) FROM post'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">queryScalar</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote>
<p>Note: 为了保持精度，即使对应的数据库列类型为数值型，所有从数据库取得的数据都被表现为字符串。</p>
</blockquote>
<h3 id="绑定参数" tabindex="-1"><a class="header-anchor" href="#绑定参数"><span>绑定参数</span></a></h3>
<p>当使用带参数的 SQL 来创建数据库命令时，建议您应该使用绑定参数的方法来防止 <a href="https://zh.wikipedia.org/wiki/SQL%E6%B3%A8%E5%85%A5" title="SQL 注入" target="_blank" rel="noopener noreferrer">SQL 注入</a> 攻击，例如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$post</span> <span class="token operator">=</span> <span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'SELECT * FROM post WHERE id=:id AND status=:status'</span><span class="token punctuation">)</span></span>
<span class="line">           <span class="token operator">-></span><span class="token function">bindValue</span><span class="token punctuation">(</span><span class="token string single-quoted-string">':id'</span><span class="token punctuation">,</span> <span class="token variable">$_GET</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'id'</span><span class="token punctuation">]</span><span class="token punctuation">)</span></span>
<span class="line">           <span class="token operator">-></span><span class="token function">bindValue</span><span class="token punctuation">(</span><span class="token string single-quoted-string">':status'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span></span>
<span class="line">           <span class="token operator">-></span><span class="token function">queryOne</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>在 SQL 语句中，你可以嵌入一个或多个参数占位符(例如，上述例子中的 <code v-pre>:id</code> )。一个参数占位符应该是以冒号开头的字符串。之后你可以调用下面绑定参数的方法来绑定参数值：</p>
<ul>
<li>[[yii\db\Command::bindValue()|bindValue()]]：绑定一个参数值</li>
<li>[[yii\db\Command::bindValues()|bindValues()]]：在一次调用中绑定多个参数值</li>
<li>[[yii\db\Command::bindParam()|bindParam()]]：与 [[yii\db\Command::bindValue()|bindValue()]] 相似，但是也支持绑定参数引用。</li>
</ul>
<p>下面的例子展示了几个可供选择的绑定参数的方法：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$params</span> <span class="token operator">=</span> <span class="token punctuation">[</span></span>
<span class="line">    <span class="token string single-quoted-string">':id'</span> <span class="token operator">=></span> <span class="token variable">$_GET</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'id'</span><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">':status'</span> <span class="token operator">=></span> <span class="token number">1</span></span>
<span class="line"><span class="token punctuation">]</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token variable">$db</span> <span class="token operator">=</span> <span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token variable">$post</span> <span class="token operator">=</span> <span class="token variable">$db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'SELECT * FROM post WHERE id=:id AND status=:status'</span><span class="token punctuation">)</span></span>
<span class="line">           <span class="token operator">-></span><span class="token function">bindValues</span><span class="token punctuation">(</span><span class="token variable">$params</span><span class="token punctuation">)</span></span>
<span class="line">           <span class="token operator">-></span><span class="token function">queryOne</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$post</span> <span class="token operator">=</span> <span class="token variable">$db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'SELECT * FROM post WHERE id=:id AND status=:status'</span><span class="token punctuation">,</span> <span class="token variable">$params</span><span class="token punctuation">)</span></span>
<span class="line">           <span class="token operator">-></span><span class="token function">queryOne</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="预处理语句" tabindex="-1"><a class="header-anchor" href="#预处理语句"><span>预处理语句</span></a></h4>
<p>绑定参数是通过 <a href="https://www.php.net/manual/zh/mysqli.quickstart.prepared-statements.php" target="_blank" rel="noopener noreferrer">预处理语句</a> 实现的，除了能防止 SQL 注入攻击，它也可以通过一次预处理 SQL 语句，使用不同参数多次执行，来提升性能。例如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$command</span> <span class="token operator">=</span> <span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'SELECT * FROM post WHERE id=:id'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$post1</span> <span class="token operator">=</span> <span class="token variable">$command</span><span class="token operator">-></span><span class="token function">bindValue</span><span class="token punctuation">(</span><span class="token string single-quoted-string">':id'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">queryOne</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token variable">$post2</span> <span class="token operator">=</span> <span class="token variable">$command</span><span class="token operator">-></span><span class="token function">bindValue</span><span class="token punctuation">(</span><span class="token string single-quoted-string">':id'</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">queryOne</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token comment">// ...</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>因为 [[yii\db\Command::bindParam()|bindParam()]] 支持通过引用来绑定参数，上述代码也可以像下面这样写：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$command</span> <span class="token operator">=</span> <span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'SELECT * FROM post WHERE id=:id'</span><span class="token punctuation">)</span></span>
<span class="line">              <span class="token operator">-></span><span class="token function">bindParam</span><span class="token punctuation">(</span><span class="token string single-quoted-string">':id'</span><span class="token punctuation">,</span> <span class="token variable">$id</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$id</span> <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token variable">$post1</span> <span class="token operator">=</span> <span class="token variable">$command</span><span class="token operator">-></span><span class="token function">queryOne</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$id</span> <span class="token operator">=</span> <span class="token number">2</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token variable">$post2</span> <span class="token operator">=</span> <span class="token variable">$command</span><span class="token operator">-></span><span class="token function">queryOne</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token comment">// ...</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>注意，在执行语句前你将占位符绑定到 <code v-pre>$id</code> 变量，然后在之后的每次执行前改变变量的值（这通常是用循环来完成的）。以这种方式执行查询比为每个不同的参数值执行一次新的查询要高效得多得多。</p>
<blockquote>
<p>Info: 参数绑定仅用于需要将值插入到包含普通SQL的字符串中的地方。 在 <a href="https://github.com/yiisoft/yii2/blob/master/docs/guide-zh-CN/db-query-builder.md" target="_blank" rel="noopener noreferrer">Query Builder</a> 和 <a href="https://github.com/yiisoft/yii2/blob/master/docs/guide-zh-CN/db-active-record.md" target="_blank" rel="noopener noreferrer">Active Record</a> 等更高抽象层的许多地方，您经常会指定一组将被转换为 SQL 的值。 在这些地方，参数绑定是由 Yii 内部完成的，因此不需要手动指定参数。</p>
</blockquote>
<h3 id="执行非查询语句" tabindex="-1"><a class="header-anchor" href="#执行非查询语句"><span>执行非查询语句</span></a></h3>
<p>上面部分中介绍的 <code v-pre>queryXyz()</code> 方法都处理的是从数据库返回数据的查询语句。对于那些不取回数据的语句，你应该调用的是 [[yii\db\Command::execute()]] 方法。<br>
对于 INSERT, UPDATE 和 DELETE 语句，不再需要写纯SQL语句了，你可以直接调用 [[yii\db\Command::insert()|insert()]]、[[yii\db\Command::update()|update()]]、[[yii\db\Command::delete()|delete()]]，来构建相应的 SQL 语句。这些方法将正确地引用表和列名称以及绑定参数值。</p>
<h4 id="insert-语句" tabindex="-1"><a class="header-anchor" href="#insert-语句"><span>INSERT 语句</span></a></h4>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">//INSERT INTO user (name, age) VALUES ('Sam', 30);</span></span>
<span class="line"><span class="token variable">$db</span> <span class="token operator">=</span> <span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token comment">//原生方式</span></span>
<span class="line"><span class="token variable">$db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token string double-quoted-string">"INSERT INTO user (name, age) VALUES ('Sam', 30)"</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">execute</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token comment">//推荐方式</span></span>
<span class="line"><span class="token variable">$db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">insert</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'user'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span></span>
<span class="line">    <span class="token string single-quoted-string">'name'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'Sam'</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">'age'</span> <span class="token operator">=></span> <span class="token number">30</span><span class="token punctuation">,</span></span>
<span class="line"><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">execute</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="update-语句" tabindex="-1"><a class="header-anchor" href="#update-语句"><span>UPDATE 语句</span></a></h4>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">//UPDATE user SET status=1 WHERE id=1 AND age>30;</span></span>
<span class="line"><span class="token variable">$db</span> <span class="token operator">=</span> <span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token comment">//原生方式</span></span>
<span class="line"><span class="token variable">$db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'UPDATE user SET status=1 WHERE id=1 AND age>30'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">execute</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token comment">//推荐方式</span></span>
<span class="line"><span class="token variable">$db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">update</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'user'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token string single-quoted-string">'status'</span> <span class="token operator">=></span> <span class="token number">1</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'id=1 AND age>30'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">execute</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token comment">//带参数</span></span>
<span class="line"><span class="token variable">$age</span> <span class="token operator">=</span> <span class="token number">30</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token variable">$db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">update</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'user'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token string single-quoted-string">'status'</span> <span class="token operator">=></span> <span class="token number">1</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'id=1 AND age>:age'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token string single-quoted-string">':age'</span> <span class="token operator">=></span> <span class="token variable">$age</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">execute</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="delete-语句" tabindex="-1"><a class="header-anchor" href="#delete-语句"><span>DELETE 语句</span></a></h4>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// DELETE FROM user WHERE status = 0;</span></span>
<span class="line"><span class="token variable">$db</span> <span class="token operator">=</span> <span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token comment">//原生方式</span></span>
<span class="line"><span class="token variable">$db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'DELETE FROM user WHERE status = 0'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">execute</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token comment">//推荐方式</span></span>
<span class="line"><span class="token variable">$db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">delete</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'user'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'status = 0'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">execute</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="批量-insert" tabindex="-1"><a class="header-anchor" href="#批量-insert"><span>批量 INSERT</span></a></h4>
<p>你也可以调用 [[yii\db\Command::batchInsert()|batchInsert()]] 来一次插入多行，这比一次插入一行要高效得多：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">//INSERT INTO user (name, age) VALUES ('Tom', 30),('Jane', 20),('Linda', 25);</span></span>
<span class="line"><span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">batchInsert</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'user'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token string single-quoted-string">'name'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'age'</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token punctuation">[</span></span>
<span class="line">    <span class="token punctuation">[</span><span class="token string single-quoted-string">'Tom'</span><span class="token punctuation">,</span> <span class="token number">30</span><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token punctuation">[</span><span class="token string single-quoted-string">'Jane'</span><span class="token punctuation">,</span> <span class="token number">20</span><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token punctuation">[</span><span class="token string single-quoted-string">'Linda'</span><span class="token punctuation">,</span> <span class="token number">25</span><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line"><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">execute</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h4 id="insert-update" tabindex="-1"><a class="header-anchor" href="#insert-update"><span>INSERT + UPDATE</span></a></h4>
<p>另一个有用的方法是 [[yii\db\Command::upsert()|upsert()]]。Upsert 是一种原子操作，如果它们不存在（匹配唯一约束），则将行插入到数据库表中，或者在它们执行时更新它们，例如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">upsert</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'pages'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span></span>
<span class="line">    <span class="token string single-quoted-string">'name'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'Front page'</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">'url'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'http://example.com/'</span><span class="token punctuation">,</span> <span class="token comment">// url is unique</span></span>
<span class="line">    <span class="token string single-quoted-string">'visits'</span> <span class="token operator">=></span> <span class="token number">0</span><span class="token punctuation">,</span></span>
<span class="line"><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token punctuation">[</span></span>
<span class="line">    <span class="token string single-quoted-string">'visits'</span> <span class="token operator">=></span> <span class="token keyword">new</span> <span class="token class-name class-name-fully-qualified"><span class="token punctuation">\</span>yii<span class="token punctuation">\</span>db<span class="token punctuation">\</span>Expression</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'visits + 1'</span><span class="token punctuation">)</span><span class="token punctuation">,</span></span>
<span class="line"><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token variable">$params</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">execute</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>上面的代码，相当于先查找 <code v-pre>url='http://example.com/'</code> 的数据，但是前提要设置 <code v-pre>url</code> 字段 <code v-pre>unique</code> 唯一索引，查询结果，如果不存在，将插入一个新的页面记录，反之，则将 <code v-pre>visits</code> 字段（访问计数器）自增 1。</p>
<p>请注意，上述的方法只是构建出语句，你总是需要调用 [[yii\db\Command::execute()|execute()]] 来真正地执行它们。[[yii\db\Command::execute()]] 方法返回执行 SQL 所影响到的行数。</p>
<h2 id="引用表和列名称" tabindex="-1"><a class="header-anchor" href="#引用表和列名称"><span>引用表和列名称</span></a></h2>
<p>当写与数据库无关的代码时，正确地引用表和列名称总是一件头疼的事， 因为不同的数据库有不同的名称引用规则，为了克服这个问题， 你可以使用下面由 Yii 提出的引用语法。</p>
<ul>
<li><code v-pre>[[column name]]</code>: 使用两对方括号来将列名括起来;</li>
<li><code v-pre>{{table name}}</code>: 使用两对大括号来将表名括起来。</li>
</ul>
<p>Yii DAO 将自动地根据数据库的具体语法来将这些结构转化为对应的被引用的列或者表名称。例如，</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// 在 MySQL 中执行该 SQL : SELECT COUNT(`id`) FROM `employee`</span></span>
<span class="line"><span class="token variable">$count</span> <span class="token operator">=</span> <span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token string double-quoted-string">"SELECT COUNT([[id]]) FROM {{employee}}"</span><span class="token punctuation">)</span></span>
<span class="line">            <span class="token operator">-></span><span class="token function">queryScalar</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="使用表前缀" tabindex="-1"><a class="header-anchor" href="#使用表前缀"><span>使用表前缀</span></a></h3>
<p>如果你的数据库表名大多都拥有一个共同的前缀， 你可以使用 Yii DAO 所提供的表前缀功能。</p>
<p>首先，通过应用配置中的 [[yii\db\Connection::tablePrefix]] 属性来指定表前缀：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token keyword">return</span> <span class="token punctuation">[</span></span>
<span class="line">    <span class="token comment">// ...</span></span>
<span class="line">    <span class="token string single-quoted-string">'components'</span> <span class="token operator">=></span> <span class="token punctuation">[</span></span>
<span class="line">        <span class="token comment">// ...</span></span>
<span class="line">        <span class="token string single-quoted-string">'db'</span> <span class="token operator">=></span> <span class="token punctuation">[</span></span>
<span class="line">            <span class="token comment">// ...</span></span>
<span class="line">            <span class="token string single-quoted-string">'tablePrefix'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'tbl_'</span><span class="token punctuation">,</span></span>
<span class="line">        <span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line"><span class="token punctuation">]</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>接着在你的代码中，当你需要涉及到一张表名中包含该前缀的表时， 应使用语法 <code v-pre>{{%table_name}}</code>。百分号将被自动地替换为你在配置 DB 组件时指定的表前缀。 例如，</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// 在 MySQL 中执行该 SQL: SELECT COUNT(`id`) FROM `tbl_employee`</span></span>
<span class="line"><span class="token variable">$count</span> <span class="token operator">=</span> <span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token string double-quoted-string">"SELECT COUNT([[id]]) FROM {{%employee}}"</span><span class="token punctuation">)</span></span>
<span class="line">            <span class="token operator">-></span><span class="token function">queryScalar</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="执行事务" tabindex="-1"><a class="header-anchor" href="#执行事务"><span>执行事务</span></a></h2>
<p>当顺序地执行多个相关的语句时，你或许需要将它们包在一个事务中来保证数据库的完整性和一致性。如果这些语句中的任何一个失败了，数据库将回滚到这些语句执行前的状态。</p>
<p>下面的代码展示了一个使用事务的典型方法：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token operator">-></span><span class="token function">transaction</span><span class="token punctuation">(</span><span class="token keyword">function</span><span class="token punctuation">(</span><span class="token variable">$db</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token variable">$db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token variable">$sql1</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">execute</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">    <span class="token variable">$db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token variable">$sql2</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">execute</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">    <span class="token comment">// ... 执行其他SQL语句 ...</span></span>
<span class="line"><span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>上述代码等价于下面的代码，但是下面的代码给予了你对于错误处理代码的更多掌控：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$db</span> <span class="token operator">=</span> <span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token variable">$transaction</span> <span class="token operator">=</span> <span class="token variable">$db</span><span class="token operator">-></span><span class="token function">beginTransaction</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token keyword">try</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token variable">$db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token variable">$sql1</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">execute</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">    <span class="token variable">$db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token variable">$sql2</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">execute</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">    <span class="token comment">// ... 执行其他SQL语句 ...</span></span>
<span class="line"></span>
<span class="line">    <span class="token variable">$transaction</span><span class="token operator">-></span><span class="token function">commit</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token punctuation">}</span> <span class="token keyword">catch</span><span class="token punctuation">(</span><span class="token class-name class-name-fully-qualified"><span class="token punctuation">\</span>Exception</span> <span class="token variable">$e</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token variable">$transaction</span><span class="token operator">-></span><span class="token function">rollBack</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">    <span class="token keyword">throw</span> <span class="token variable">$e</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token punctuation">}</span> <span class="token keyword">catch</span><span class="token punctuation">(</span><span class="token class-name class-name-fully-qualified"><span class="token punctuation">\</span>Throwable</span> <span class="token variable">$e</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token variable">$transaction</span><span class="token operator">-></span><span class="token function">rollBack</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">    <span class="token keyword">throw</span> <span class="token variable">$e</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token punctuation">}</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>通过调用 [[yii\db\Connection::beginTransaction()|beginTransaction()]] 方法，一个新事务开始了。事务被表示为一个存储在 <code v-pre>$transaction</code> 变量中的 [[yii\db\Transaction]] 对象。然后，被执行的语句都被包含在一个 <code v-pre>try...catch...</code> 块中。如果所有的语句都被成功地执行了， [[yii\db\Transaction::commit()|commit()]] 将被调用来提交这个事务。否则，如果异常被触发并被捕获， [[yii\db\Transaction::rollBack()|rollBack()]] 方法将被调用，来回滚事务中失败语句之前所有语句所造成的改变。<code v-pre>throw $e</code> 将重新抛出该异常，就好像我们没有捕获它一样，因此正常的错误处理程序将处理它。</p>
<h3 id="指定隔离级别" tabindex="-1"><a class="header-anchor" href="#指定隔离级别"><span>指定隔离级别</span></a></h3>
<p>Yii 也支持为你的事务设置 <a href="http://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Isolation_levels" target="_blank" rel="noopener noreferrer">隔离级别</a> 。默认情况下，当我们开启一个新事务，它将使用你的数据库所设定的隔离级别。你也可以重载默认的隔离级别，向下面这样：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$isolationLevel</span> <span class="token operator">=</span> <span class="token class-name class-name-fully-qualified static-context"><span class="token punctuation">\</span>yii<span class="token punctuation">\</span>db<span class="token punctuation">\</span>Transaction</span><span class="token operator">::</span><span class="token constant">REPEATABLE_READ</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token operator">-></span><span class="token function">transaction</span><span class="token punctuation">(</span><span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token variable">$db</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token operator">...</span><span class="token operator">.</span></span>
<span class="line"><span class="token punctuation">}</span><span class="token punctuation">,</span> <span class="token variable">$isolationLevel</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// or alternatively</span></span>
<span class="line"></span>
<span class="line"><span class="token variable">$transaction</span> <span class="token operator">=</span> <span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token operator">-></span><span class="token function">beginTransaction</span><span class="token punctuation">(</span><span class="token variable">$isolationLevel</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>Yii 为四个最常用的隔离级别提供了常量：</p>
<ul>
<li>[[\yii\db\Transaction::READ_UNCOMMITTED]] - 最弱的隔离级别，脏读、不可重复读以及幻读都可能发生。</li>
<li>[[\yii\db\Transaction::READ_COMMITTED]] - 避免了脏读。</li>
<li>[[\yii\db\Transaction::REPEATABLE_READ]] - 避免了脏读和不可重复读。</li>
<li>[[\yii\db\Transaction::SERIALIZABLE]] - 最强的隔离级别， 避免了上述所有的问题。</li>
</ul>
<p>除了使用上述的常量来指定隔离级别，你还可以使用你的数据库所支持的具有有效语法的字符串。比如，在 PostgreSQL 中，你可以使用 <code v-pre>SERIALIZABLE READ ONLY DEFERRABLE</code>。</p>
<p>请注意，一些数据库只允许为整个连接设置隔离级别，即使你之后什么也没指定，后来的事务都将获得与之前相同的隔离级别。使用此功能时，你需要为所有的事务明确地设置隔离级别来避免冲突的设置。在本文写作之时，只有 MSSQL 和 SQLite 受这些限制的影响。</p>
<blockquote>
<p>Note: SQLite 只支持两种隔离级别，所以你只能使用 <code v-pre>READ UNCOMMITTED</code> 和 <code v-pre>SERIALIZABLE</code>，使用其他级别将导致异常的抛出。</p>
</blockquote>
<blockquote>
<p>Note: PostgreSQL 不支持在事务开启前设定隔离级别，因此，你不能在开启事务时直接指定隔离级别，你必须在事务开始后再调用 [[yii\db\Transaction::setIsolationLevel()]]。</p>
</blockquote>
<h3 id="嵌套事务" tabindex="-1"><a class="header-anchor" href="#嵌套事务"><span>嵌套事务</span></a></h3>
<p>如果你的数据库支持保存点，你可以像下面这样嵌套多个事务：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token operator">-></span><span class="token function">transaction</span><span class="token punctuation">(</span><span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token variable">$db</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token comment">// 外部事务</span></span>
<span class="line"></span>
<span class="line">    <span class="token variable">$db</span><span class="token operator">-></span><span class="token function">transaction</span><span class="token punctuation">(</span><span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token variable">$db</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">        <span class="token comment">// 内部事务</span></span>
<span class="line">    <span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>或者：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$db</span> <span class="token operator">=</span> <span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token variable">$outerTransaction</span> <span class="token operator">=</span> <span class="token variable">$db</span><span class="token operator">-></span><span class="token function">beginTransaction</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token keyword">try</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token variable">$db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token variable">$sql1</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">execute</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line">    <span class="token variable">$innerTransaction</span> <span class="token operator">=</span> <span class="token variable">$db</span><span class="token operator">-></span><span class="token function">beginTransaction</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">    <span class="token keyword">try</span> <span class="token punctuation">{</span></span>
<span class="line">        <span class="token variable">$db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token variable">$sql2</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">execute</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">        <span class="token variable">$innerTransaction</span><span class="token operator">-></span><span class="token function">commit</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">    <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name class-name-fully-qualified"><span class="token punctuation">\</span>Exception</span> <span class="token variable">$e</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">        <span class="token variable">$innerTransaction</span><span class="token operator">-></span><span class="token function">rollBack</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">        <span class="token keyword">throw</span> <span class="token variable">$e</span><span class="token punctuation">;</span></span>
<span class="line">    <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name class-name-fully-qualified"><span class="token punctuation">\</span>Throwable</span> <span class="token variable">$e</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">        <span class="token variable">$innerTransaction</span><span class="token operator">-></span><span class="token function">rollBack</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">        <span class="token keyword">throw</span> <span class="token variable">$e</span><span class="token punctuation">;</span></span>
<span class="line">    <span class="token punctuation">}</span></span>
<span class="line"></span>
<span class="line">    <span class="token variable">$outerTransaction</span><span class="token operator">-></span><span class="token function">commit</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name class-name-fully-qualified"><span class="token punctuation">\</span>Exception</span> <span class="token variable">$e</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token variable">$outerTransaction</span><span class="token operator">-></span><span class="token function">rollBack</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">    <span class="token keyword">throw</span> <span class="token variable">$e</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name class-name-fully-qualified"><span class="token punctuation">\</span>Throwable</span> <span class="token variable">$e</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token variable">$outerTransaction</span><span class="token operator">-></span><span class="token function">rollBack</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">    <span class="token keyword">throw</span> <span class="token variable">$e</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token punctuation">}</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h2 id="主从复制和读写分离" tabindex="-1"><a class="header-anchor" href="#主从复制和读写分离"><span>主从复制和读写分离</span></a></h2>
<p>许多数据库支持 <a href="http://en.wikipedia.org/wiki/Replication_(computing)#Database_replication" target="_blank" rel="noopener noreferrer">数据库复制</a> 来获得更好的数据库可用性，以及更快的服务器响应时间。通过数据库复制功能，数据从所谓的主服务器被复制到从服务器。所有的写和更新必须发生在主服务器上，而读可以发生在从服务器上。</p>
<h3 id="一主多从" tabindex="-1"><a class="header-anchor" href="#一主多从"><span>一主多从</span></a></h3>
<p>为了利用数据库主从复制并且完成读写分离， 你可以按照下面的方法来配置 [[yii\db\Connection]] 组件：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token punctuation">[</span></span>
<span class="line">    <span class="token string single-quoted-string">'class'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'yii\db\Connection'</span><span class="token punctuation">,</span></span>
<span class="line"></span>
<span class="line">    <span class="token comment">// 主库的配置</span></span>
<span class="line">    <span class="token string single-quoted-string">'dsn'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'dsn for master server'</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">'username'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'master'</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">'password'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">''</span><span class="token punctuation">,</span></span>
<span class="line"></span>
<span class="line">    <span class="token comment">// 从库的通用配置</span></span>
<span class="line">    <span class="token string single-quoted-string">'slaveConfig'</span> <span class="token operator">=></span> <span class="token punctuation">[</span></span>
<span class="line">        <span class="token string single-quoted-string">'username'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'slave'</span><span class="token punctuation">,</span></span>
<span class="line">        <span class="token string single-quoted-string">'password'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">''</span><span class="token punctuation">,</span></span>
<span class="line">        <span class="token string single-quoted-string">'attributes'</span> <span class="token operator">=></span> <span class="token punctuation">[</span></span>
<span class="line">            <span class="token comment">// 使用一个更小的连接超时</span></span>
<span class="line">            <span class="token class-name static-context">PDO</span><span class="token operator">::</span><span class="token constant">ATTR_TIMEOUT</span> <span class="token operator">=></span> <span class="token number">10</span><span class="token punctuation">,</span></span>
<span class="line">        <span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line"></span>
<span class="line">    <span class="token comment">// 从库的配置列表</span></span>
<span class="line">    <span class="token string single-quoted-string">'slaves'</span> <span class="token operator">=></span> <span class="token punctuation">[</span></span>
<span class="line">        <span class="token punctuation">[</span><span class="token string single-quoted-string">'dsn'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'dsn for slave server 1'</span><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">        <span class="token punctuation">[</span><span class="token string single-quoted-string">'dsn'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'dsn for slave server 2'</span><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">        <span class="token punctuation">[</span><span class="token string single-quoted-string">'dsn'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'dsn for slave server 3'</span><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">        <span class="token punctuation">[</span><span class="token string single-quoted-string">'dsn'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'dsn for slave server 4'</span><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line"><span class="token punctuation">]</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>上述的配置指定了一主多从的设置。这些从库其中之一将被建立起连接并执行读操作，而主库将被用来执行写操作。这样的读写分离将通过上述配置自动地完成。比如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token comment">// 使用上述配置来创建一个 Connection 实例</span></span>
<span class="line"><span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span> <span class="token operator">=</span> <span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token function">createObject</span><span class="token punctuation">(</span><span class="token variable">$config</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// 在从库中的一个上执行语句</span></span>
<span class="line"><span class="token variable">$rows</span> <span class="token operator">=</span> <span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'SELECT * FROM user LIMIT 10'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">queryAll</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token comment">// 在主库上执行语句</span></span>
<span class="line"><span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token string double-quoted-string">"UPDATE user SET username='demo' WHERE id=1"</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">execute</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><blockquote>
<p>Info: 通过调用 [[yii\db\Command::execute()]] 来执行的语句都被视为写操作，而其他所有通过调用 [[yii\db\Command]] 中任一 “query” 方法来执行的语句都被视为读操作。你可以通过 <code v-pre>Yii::$app-&gt;db-&gt;slave</code> 来获取当前有效的从库连接。</p>
</blockquote>
<p><code v-pre>Connection</code> 组件支持从库间的负载均衡和失效备援，当第一次执行读操作时，<code v-pre>Connection</code> 组件将随机地挑选出一个从库并尝试与之建立连接，如果这个从库被发现为“挂掉的”，将尝试连接另一个从库。如果没有一个从库是连接得上的，那么将试着连接到主库上。通过配置 [[yii\db\Connection::serverStatusCache|server status cache]]，一个“挂掉的”服务器将会被记住，因此，在一个 yii\db\Connection::serverRetryInterval 内将不再试着连接该服务器。</p>
<blockquote>
<p>Info: 在上面的配置中，每个从库都共同地指定了 10 秒的连接超时时间，这意味着，如果一个从库在 10 秒内不能被连接上，它将被视为“挂掉的”。你可以根据你的实际环境来调整该参数。</p>
</blockquote>
<h3 id="多主多从" tabindex="-1"><a class="header-anchor" href="#多主多从"><span>多主多从</span></a></h3>
<p>你也可以配置多主多从。例如，</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token punctuation">[</span></span>
<span class="line">    <span class="token string single-quoted-string">'class'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'yii\db\Connection'</span><span class="token punctuation">,</span></span>
<span class="line"></span>
<span class="line">    <span class="token comment">// 主库通用的配置</span></span>
<span class="line">    <span class="token string single-quoted-string">'masterConfig'</span> <span class="token operator">=></span> <span class="token punctuation">[</span></span>
<span class="line">        <span class="token string single-quoted-string">'username'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'master'</span><span class="token punctuation">,</span></span>
<span class="line">        <span class="token string single-quoted-string">'password'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">''</span><span class="token punctuation">,</span></span>
<span class="line">        <span class="token string single-quoted-string">'attributes'</span> <span class="token operator">=></span> <span class="token punctuation">[</span></span>
<span class="line">            <span class="token comment">// use a smaller connection timeout</span></span>
<span class="line">            <span class="token class-name static-context">PDO</span><span class="token operator">::</span><span class="token constant">ATTR_TIMEOUT</span> <span class="token operator">=></span> <span class="token number">10</span><span class="token punctuation">,</span></span>
<span class="line">        <span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line"></span>
<span class="line">    <span class="token comment">// 主库配置列表</span></span>
<span class="line">    <span class="token string single-quoted-string">'masters'</span> <span class="token operator">=></span> <span class="token punctuation">[</span></span>
<span class="line">        <span class="token punctuation">[</span><span class="token string single-quoted-string">'dsn'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'dsn for master server 1'</span><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">        <span class="token punctuation">[</span><span class="token string single-quoted-string">'dsn'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'dsn for master server 2'</span><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line"></span>
<span class="line">    <span class="token comment">// 从库的通用配置</span></span>
<span class="line">    <span class="token string single-quoted-string">'slaveConfig'</span> <span class="token operator">=></span> <span class="token punctuation">[</span></span>
<span class="line">        <span class="token string single-quoted-string">'username'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'slave'</span><span class="token punctuation">,</span></span>
<span class="line">        <span class="token string single-quoted-string">'password'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">''</span><span class="token punctuation">,</span></span>
<span class="line">        <span class="token string single-quoted-string">'attributes'</span> <span class="token operator">=></span> <span class="token punctuation">[</span></span>
<span class="line">            <span class="token comment">// use a smaller connection timeout</span></span>
<span class="line">            <span class="token class-name static-context">PDO</span><span class="token operator">::</span><span class="token constant">ATTR_TIMEOUT</span> <span class="token operator">=></span> <span class="token number">10</span><span class="token punctuation">,</span></span>
<span class="line">        <span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line"></span>
<span class="line">    <span class="token comment">// 从库配置列表</span></span>
<span class="line">    <span class="token string single-quoted-string">'slaves'</span> <span class="token operator">=></span> <span class="token punctuation">[</span></span>
<span class="line">        <span class="token punctuation">[</span><span class="token string single-quoted-string">'dsn'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'dsn for slave server 1'</span><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">        <span class="token punctuation">[</span><span class="token string single-quoted-string">'dsn'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'dsn for slave server 2'</span><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">        <span class="token punctuation">[</span><span class="token string single-quoted-string">'dsn'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'dsn for slave server 3'</span><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">        <span class="token punctuation">[</span><span class="token string single-quoted-string">'dsn'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'dsn for slave server 4'</span><span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token punctuation">]</span><span class="token punctuation">,</span></span>
<span class="line"><span class="token punctuation">]</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>上述配置指定了两个主库和两个从库。<code v-pre>Connection</code> 组件在主库之间，也支持如从库间般的负载均衡和失效备援。唯一的差别是，如果没有主库可用，将抛出一个异常。</p>
<blockquote>
<p>Note: 当你使用 [[yii\db\Connection::masters|masters]] 属性来配置一个或多个主库时， 所有其他指定数据库连接的属性 (例如 <code v-pre>dsn</code>, <code v-pre>username</code>, <code v-pre>password</code>) 与 <code v-pre>Connection</code> 对象本身将被忽略。</p>
</blockquote>
<p>默认情况下，事务使用主库连接，一个事务内，所有的数据库操作都将使用主库连接，例如，</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$db</span> <span class="token operator">=</span> <span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token comment">// 在主库上启动事务</span></span>
<span class="line"><span class="token variable">$transaction</span> <span class="token operator">=</span> <span class="token variable">$db</span><span class="token operator">-></span><span class="token function">beginTransaction</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line"><span class="token keyword">try</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token comment">// 两个语句都是在主库上执行的</span></span>
<span class="line">    <span class="token variable">$rows</span> <span class="token operator">=</span> <span class="token variable">$db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'SELECT * FROM user LIMIT 10'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">queryAll</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">    <span class="token variable">$db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token string double-quoted-string">"UPDATE user SET username='demo' WHERE id=1"</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">execute</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span>
<span class="line">    <span class="token variable">$transaction</span><span class="token operator">-></span><span class="token function">commit</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token punctuation">}</span> <span class="token keyword">catch</span><span class="token punctuation">(</span><span class="token class-name class-name-fully-qualified"><span class="token punctuation">\</span>Exception</span> <span class="token variable">$e</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token variable">$transaction</span><span class="token operator">-></span><span class="token function">rollBack</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">    <span class="token keyword">throw</span> <span class="token variable">$e</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token punctuation">}</span> <span class="token keyword">catch</span><span class="token punctuation">(</span><span class="token class-name class-name-fully-qualified"><span class="token punctuation">\</span>Throwable</span> <span class="token variable">$e</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token variable">$transaction</span><span class="token operator">-></span><span class="token function">rollBack</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line">    <span class="token keyword">throw</span> <span class="token variable">$e</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token punctuation">}</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>如果你想在从库上开启事务，你应该明确地像下面这样做：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$transaction</span> <span class="token operator">=</span> <span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token operator">-></span><span class="token property">slave</span><span class="token operator">-></span><span class="token function">beginTransaction</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div></div></div><p>有时，你或许想要强制使用主库来执行读查询。这可以通过 <code v-pre>useMaster()</code> 方法来完成：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$rows</span> <span class="token operator">=</span> <span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token operator">-></span><span class="token function">useMaster</span><span class="token punctuation">(</span><span class="token keyword">function</span> <span class="token punctuation">(</span><span class="token variable">$db</span><span class="token punctuation">)</span> <span class="token punctuation">{</span></span>
<span class="line">    <span class="token keyword">return</span> <span class="token variable">$db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'SELECT * FROM user LIMIT 10'</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">queryAll</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"><span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>你也可以明确地将 <code v-pre>Yii::$app-&gt;db-&gt;enableSlaves</code> 设置为 false 来将所有的读操作指向主库连接。</p>
<h2 id="使用数据库模式" tabindex="-1"><a class="header-anchor" href="#使用数据库模式"><span>使用数据库模式</span></a></h2>
<p>Yii DAO 提供了一套完整的方法来让你操纵数据库模式，如创建表、从表中删除一列，等等。这些方法罗列如下：</p>
<ul>
<li>[[yii\db\Command::createTable()|createTable()]]：创建一张表</li>
<li>[[yii\db\Command::renameTable()|renameTable()]]：重命名一张表</li>
<li>[[yii\db\Command::dropTable()|dropTable()]]：删除一张表</li>
<li>[[yii\db\Command::truncateTable()|truncateTable()]]：删除一张表中的所有行</li>
<li>[[yii\db\Command::addColumn()|addColumn()]]：增加一列</li>
<li>[[yii\db\Command::renameColumn()|renameColumn()]]：重命名一列</li>
<li>[[yii\db\Command::dropColumn()|dropColumn()]]：删除一列</li>
<li>[[yii\db\Command::alterColumn()|alterColumn()]]：修改一列</li>
<li>[[yii\db\Command::addPrimaryKey()|addPrimaryKey()]]：增加主键</li>
<li>[[yii\db\Command::dropPrimaryKey()|dropPrimaryKey()]]：删除主键</li>
<li>[[yii\db\Command::addForeignKey()|addForeignKey()]]：增加一个外键</li>
<li>[[yii\db\Command::dropForeignKey()|dropForeignKey()]]：删除一个外键</li>
<li>[[yii\db\Command::createIndex()|createIndex()]]：增加一个索引</li>
<li>[[yii\db\Command::dropIndex()|dropIndex()]]：删除一个索引</li>
</ul>
<p>这些方法可以如下地使用：</p>
<h3 id="创建一张表" tabindex="-1"><a class="header-anchor" href="#创建一张表"><span>创建一张表</span></a></h3>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token operator">-></span><span class="token function">createCommand</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token operator">-></span><span class="token function">createTable</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'post'</span><span class="token punctuation">,</span> <span class="token punctuation">[</span></span>
<span class="line">    <span class="token string single-quoted-string">'id'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'pk'</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">'title'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'string'</span><span class="token punctuation">,</span></span>
<span class="line">    <span class="token string single-quoted-string">'text'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'text'</span><span class="token punctuation">,</span></span>
<span class="line"><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>上面的数组描述要创建的列的名称和类型。对于列的类型，Yii 提供了一套抽象数据类型来允许你定义出数据库无关的模式。这些将根据表所在数据库的种类，被转换为特定的类型定义。请参考 [[yii\db\Command::createTable()|createTable()]]-method 的 API 文档来获取更多信息。</p>
<h3 id="检索表的定义信息" tabindex="-1"><a class="header-anchor" href="#检索表的定义信息"><span>检索表的定义信息</span></a></h3>
<p>除了改变数据库模式，你也可以通过 DB Connection 的 [[yii\db\Connection::getTableSchema()|getTableSchema()]] 方法来检索某张表的定义信息。例如：</p>
<div class="language-php line-numbers-mode" data-highlighter="prismjs" data-ext="php" data-title="php"><pre v-pre class="language-php"><code><span class="line"><span class="token variable">$table</span> <span class="token operator">=</span> <span class="token class-name static-context">Yii</span><span class="token operator">::</span><span class="token variable">$app</span><span class="token operator">-></span><span class="token property">db</span><span class="token operator">-></span><span class="token function">getTableSchema</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'post'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>
<span class="line"></span></code></pre>
<div class="line-numbers" aria-hidden="true" style="counter-reset:line-number 0"><div class="line-number"></div></div></div><p>该方法返回一个 [[yii\db\TableSchema]] 对象， 它包含了表中的列、主键、外键，等等的信息。 所有的这些信息主要被 <a href="https://github.com/yiisoft/yii2/blob/master/docs/guide-zh-CN/db-query-builder.md" target="_blank" rel="noopener noreferrer">Query Builder</a> 和 <a href="https://github.com/yiisoft/yii2/blob/master/docs/guide-zh-CN/db-active-record.md" target="_blank" rel="noopener noreferrer">Active Record</a> 所使用，来帮助你写出数据库无关的代码。</p>
<blockquote>
<p>💖喜欢本文档的，欢迎点赞、收藏、留言或转发，谢谢支持！<br>
作者邮箱：zhuzixian520@126.com，github地址：<a href="https://github.com/zhuzixian520" target="_blank" rel="noopener noreferrer">github.com/zhuzixian520</a></p>
</blockquote>
</div></template>


